PC-SIG: World of Education

home *** CD-ROM | disk | FTP | other *** search

/ PC-SIG: World of Education / PC-SiG's World of Education.iso / run / 3312 / fe.exe / CHPT6.TXT < prev next >

Wrap

Text File | 1992-03-08 | 34.5 KB | 811 lines

page 6-1 CHAPTER 6: EVALUATION OF CELLS This chapter looks in detail at how FREE & EASY evaluates cells (or calculates the value of cells). The evaluation of cells is actually very straightforward. However, if you understand the way in which the cells are evaluated in detail, this will allow you to optimize the calculations in your sheets. FREE & EASY also permits you to change the way in which a sheet is calculated. Thus, you can customize FREE & EASY calculations to suit your own needs. This chapter assumes you know what cells are, what cell contents are and what variables are. (See chapters 2 and 3) When a Cell Is Evaluated Any cell is automatically evaluated when it is added to the sheet or when it is modified. (The only exception to this occurs when a group of cells is added to the sheet with the "Table of cells" feature, or when an editing function is used to add cell(s) to the sheet). There are also several other useful ways to evaluate a cell or a group of cells. These are discussed later in the chapter. Elementary Cell Evaluation The first step in evaluating a cell is to enter a valid expression in the cell contents. When you add or modify cell contents FREE & EASY checks to see that you have used the correct syntax and shows syntax errors as they arise. A complete list of syntax errors is in Appendix B. Whenever you get an error message, remember that you can always press F1 to bring up a help screen with further explanation. In general, any algebraically correct expression can be entered in the cell contents. The contents are evaluated from left to right. The following five operators are allowed: The operator with highest priority is ^, raise to the power. Some examples are: 2^3=8 4^3=64 The operators with the next highest priority are multiplication (*) and division (/). Some examples are: 3*2.5=7.5 5/4=1.25 3*6/2=9 The operators with the least priority are addition (+) and subtraction (-). Some examples of these are: 2.5 + 3.6 = 6.1 5.7 - 1.9 = 3.8 3.9 - 1.2 + 2.7 = 5.4 page 6-2 The following examples of cell contents demonstrate the priority of the five operators: 2 + 3^2/4=4.25 -2.5*6=15 1 + 3/2 =2.5 Brackets, i.e. "(" and ")" can be used to change the priority order of operations. You must use round brackets and not the square brackets, i.e. not "[" or curly brackets i.e. not "{". Some examples of cell contents with and without brackets are: 2 + 2^3 = 10 (2+2)^3=64 7 + 3*2 =13 (7+3)*2=20 In FREE & EASY the only limit to the number of brackets, or nesting of brackets, is the 200 character size limit on the cell contents. You should note also that when you enter cell contents with brackets, FREE & EASY checks to make sure that the brackets balance, and if they do not gives the "brackets do not balance" syntax error. When you use a number in FREE & EASY, its maximum size is bounded by 1.0E-300, 1.0E+300, -1.0E-300 and -1.0E+300. If you try to enter a number beyond these bounds, you will get an "invalid number" error message. When a cell is evaluated, its value is displayed in the cell itself according to the format for that cell. Note that the display value and cell value are not always exactly the same (see Chapter 3). Evaluation Order of Cells As mentioned, whenever a cell is added to the sheet or an existing cell is modified, it is automatically evaluated. At any time the F9 key can be pressed and all cells in all sheets will be evaluated. As FREE & EASY is evaluating the cells, it displays a message indicating which cell is presently being evaluated. The cells on the sheets are evaluated as you would read a page. Cells are evaluated from left to right for each line of the sheet, and the lines are evaluated from top to bottom. If more than one sheet is loaded into FREE & EASY, the sheets are evaluated in numerical order. The title of the sheet at the top of the window shows evaluation order of the sheet. For example a title of "3:FILE.FRE" means that the sheet entitled "FILE.FRE" is the third sheet evaluated. The order in which cells are evaluated should be kept in mind when setting up your sheets. As new sheets are started or loaded from disk, they are added to the end of the evaluation order of the sheets. This can be changed, however. See the "Change Evaluation Order of Sheets" section of this chapter for further details. page 6-3 Reference to Other Cells by Location It is possible to use the value of another cell in the cell contents. This cell value can be referred to by the location of that cell in the sheet or by the cell name. The text on page 3-3 of the Instruction Book tells you the syntax to use in order to refer to a location on the sheet as well as how to use the short-cut method. If the evaluation default "checK cell evaluation order" is set to "yes" and the cell location you choose is below or to the right of the cell whose contents you are entering, the warning "the cell referred to is out of evaluation order" is given. If the location is on a sheet numbered after the cell whose contents you are entering, you will get the error message "cell on this sheet is out of evaluation order." If the cell is evaluated (by pressing F9 for example) and the cell at a location referred to is not in the right order, an error message is given if the evaluation default "check cell order" is set to "yes". Reference to Values in the Sheet It is also possible to use the value (number) at a location on the sheet in the cell contents. The procedure for entering this location is the same as for entering the location of a cell on the sheet. When you enter this location, if there is no cell or number at the location you choose, a "no cell or valid number at this location" warning is given. When you evaluate the cell, if there is a location with no cell or valid number which is in the cell contents, an error message is given. When you evaluate a cell and the cell contents contains a reference to a sheet location which has a cell, in general there is also a value on the sheet at this location, i.e. the display value of the cell. FREE & EASY will use the actual value of the cell in the calculations and not the value displayed (remember that the value of the cell and its displayed value are not always exactly the same due to the cell format). Referencing a Cell Value by Name or a Variable Value When you add a cell or modify the cell contents and you include a cell name or variable, FREE & EASY does not check to see whether the cell or variable can be found on the sheet or one of the other sheets loaded. The search is not made until the cell is evaluated. When FREE & EASY is evaluating a cell and the cell contents contain a name, a search is started for a cell by that name or a variable by that name. Whether the search is started for a cell of that name or for a variable of that name depends on the "evaluation defaults" (see section in this chapter on this topic). See chapter 3 for how a variable is defined in the sheet. The search always begins in the sheet in which the cell being evaluated is located. All cells are checked for the page 6-4 name. The search for the variable begins at the cell being evaluated and works backwards in the sheet, i.e. to the left of the cell then up one line (just the opposite of how you would read a page). If the top left corner of the sheet is reached without finding a variable defined by that name, the search continues on the same sheet starting at the bottom right corner of the sheet and working back to the cell. If no cell or variable by that name has been found on the sheet and there are other sheets loaded and the evaluation defaults permit, a search is started in the sheet one less in the evaluation order of sheets. If the cell with the name is found in sheet 3 and four sheets are loaded, the order of search for the name would be sheet 3, 2, 1 and then 4. The sheets are searched from bottom right corner to top left corner. If FREE & EASY cannot find a cell name or variable by the name found in the cell contents, then an evaluation error is given for that cell. FREE & EASY allows a variable and cell to have the same name, even in the same sheet. Whether the cell value or variable value is used to calculate the cell value depends on the evaluation defaults. i.e. The search is stopped as soon as a cell or variable by that name is found in the sheet(s). Even though it is allowed to have a cell and variable of the same name, this is not recommended as it can lead to confusion. Redefining of Variables FREE & EASY allows you to define the same variable more than once, even on the same sheet. However, if you do this, it is recommended that you have a specific reason as it can lead to confusion. If the same variable is defined above and below the cell in which it is referred to, the definition above the cell is used. If defined to the right and left of the cell, the definition to the left of the cell is used. (Examining how FREE & EASY searches for variables as described in the previous section defines why this is so.) If the cell is in sheet 3 and the variable is defined in sheet 2 and sheet 4, the variable defined in sheet 2 is used. Refer to Variable Name or Sheet Location? When you wish to use a variable in the cell contents, you may define the variable and use its name in the cell contents. Alternately, you can refer to a number by its location in the sheet. There are advantages and disadvantages to each method. If you refer to a value (number) in the sheet by its location, FREE & EASY can find this value very quickly since it does not have to search a sheet(s) for the variable. However, a sheet location in the cell contents is not as straightforward to understand as a name. Also, remember that sheet locations in the cell contents can be relative to the cell or absolute addresses. If you move the cell, a relative sheet address found in the cell contents moves accordingly. (See chapter 4 for more on this.) If you use a variable name in the cell contents, then the cell contents can be much easier to understand, especially if you give the variable a meaningful name. However, since FREE & EASY must find the variable to page 6-5 evaluate the cell, the time taken to evaluate the cell can be longer. This time can be reduced by putting the variable as close to the cell as possible ( and to the left or above the cell). You can also move the cell without problem since the reference to the value is by a variable. (This is not necessarily true if the variable is defined in more than one location on the sheets.) Refer to a Cell Value by Cell Name or Location In the cell contents, you can refer to the value of another cell by either the other cell's name or its location. Unlike the previous section, in this case there is little difference to speed of evaluation. If you refer to the value of a cell by name, the contents may be more easily understood, especially if that cell has a meaningful name. As with referring to numbers by location, if you refer to a cell by its location on the sheet and you use a relative location, when you move either cell, you will not be referring to the cell you want. This can lead to unforeseen results or an evaluation error. Functions There are several functions available for use in the cell contents. All functions return a value which is used in calculating the value of the cell. All FREE & EASY functions are three letters long. When entering the cell contents, all functions are converted to upper case. Note that variable names, cell names and range names are not automatically converted to upper case. i.e. These names are case sensitive. You might wish to keep all these names in lower case; with all functions in upper case. The cell contents will be easier to read in this way. A function's argument must be in brackets; otherwise an error message will be given when you try to enter the cell contents. For example, SIN(45) is acceptable for cell contents, but SIN45 in unacceptable. There are three types of functions available--mathematical functions, range functions (more on ranges later in this chapter) and financial functions. Many of the functions have limits on what the value of the argument can be. A complete listing of all functions available is found in Appendix A. Mathematical Functions All the common mathematical functions are available. These include the trig functions (sine (SIN), cosine (COS) and tangent (TAN)), the hyperbolic trig functions (hyperbolic sine (SNH), hyperbolic cosine (CSH) and hyperbolic tangent (TNH)) and inverse trig functions (arc sine (ASN), arc cosine (ACS) and arc tangent (ATN)). As well, there are the functions absolute value (ABS), natural logarithm (LNN), base 10 logarithm (LOG), square root (SQR) and exponential (EXP). It is possible to have brackets within brackets for mathematical functions. page 6-6 For example, SIN((30+15)/2) is acceptable. A function's argument must evaluate to a valid FREE & EASY number or else an error message is given upon cell evaluation. As well, many functions have other restrictions which limit what the argument can be. If outside of these limits, an error message is given. If there is a problem with the function, FREE & EASY tells what the problem is and which function is causing the problem. The following errors can occur: A "domain error" occurs if the argument of the function is outside of what is mathematically allowed. For example, the arc sine function (ASN) only allows an argument of -l.0 to +1.0. Outside of this gives an error. A "loss of accuracy" error occurs if the argument for a function is so large or so small that FREE & EASY cannot accurately calculate the value of the function. For example the sine function (SIN) has no mathematical limit on what its argument can be, but FREE & EASY will give this error message if the argument is greater than 1.0E8 or less than -1.0E8. An "overflow at function" error occurs if the evaluation of the function will give results greater than the limits of FREE & EASY. For example, the exponential function (EXP) has no mathematical limit on what its argument can be, but FREE & EASY will give this error message if the argument is greater than 700. This is because FREE & EASY cannot handle numbers larger than 1.0E300. All the trig functions can be handled in radians, degrees or gradients. Which is used depends on the evaluation defaults. (This will be discussed in further detail later in this chapter.) Range Functions Several functions work with many values at once or a range of values. FREE & EASY allows you to define a rectangular area of the sheet called a range (see section on ranges in this chapter for further details.) A range function will work with all values found in the range and return a value. The range function takes a range name or range address as its argument. The values found in the range can be in the form of numbers, variable name, cell name or cells. If the range contains cells, make sure that the cell contents containing the range function are after the range, i.e. so the cell in the range is evaluated before the range function is evaluated. Most range functions are statistical functions. They are as follows: CNT: returns the total number of entries in the range AVR: returns the average value of values found in the range SUM: returns the total value of values found in the range MIN: returns the minimum value found in the range MAX: returns the maximum value found in the range STD: returns the standard deviation of values found in the range VAR: returns the variance of values found in the range page 6-7 There are several evaluation errors which can occur with range functions. If there is no range by the name given in the range argument, the "cannot find the range" error will occur. Many range functions require a range with at least one value. If the range function AVR has no values in its range, the "no numbers in the range" error will occur. Some range functions require at least two values. If the range function STD has only one value the "needs at least two values in the range" error is given. If during the calculation, the value of the range function gets too large (greater than 1.0E300), the "overflow at range" error message is given. Financial Functions Several financial functions are available. These functions differ from mathematical functions in that they take several arguments. For example, one financial function is FVA(100, 10, 5) and it takes three arguments as shown. The arguments are separated by commas. Each argument can be a number or any valid FREE & EASY expression. Although brackets are required for all the arguments together, brackets are not required for individual arguments. When a financial function is entered in the cell contents, FREE & EASY checks that the correct number of arguments are in the function. If not, a syntax error is given. When evaluating the function, FREE & EASY may yield an "overflow at function" error message if in the calculation of results, a value is encountered that exceeds FREE & EASY's limits. In financial functions, many of the arguments should be integers, for example if "number of years" is required. There is no check provided if this is not the case, and unpredictable results could occur. The financial functions are: term deposit: TDP (principle, interest, years) future value of annuity: FVA (payment, interest, years, payments per year) present value of annuity: PVA (payment, interest, years, payments per year) mortgage payments: MTG (principle, interest, years, payments per year, compounds per year) straight line depreciation: SLN (cost, salvage, years) double declining balance depreciation: DDB (cost, salvage, years, period, rate) sum of the year's digits depreciation: SYD (cost, salvage, years, period) In all cases, interest is entered in per cent. Methods to Evaluate Cells FREE & EASY provides several ways to evaluate the cells in the sheet(s). When you add a new cell or modify an existing cell, the cell is automatically evaluated. There are several other ways to evaluate cells. All of these are found in the "Eval" (evaluate) pull-down menu. page 6-8 Each of these methods of evaluation has a short cut key which you will probably use more than the menu. Even though a cell is evaluated when it is added or modified, you might want to reevaluate it when you change a variable, another cell, etc. A single cell is evaluated with the F7 key. You simply place the cursor anywhere on the cell and press F7. The cell is then evaluated. If you want the cursor to go to the next cell to be evaluated, press F8. If you want the cursor to go to the previous cell to be evaluated, press shift-F8. The cursor will go to the next or previous sheet in the evaluation order if necessary to get to the next or previous cell to be evaluated. This combination of keys (F7, F8 and shift-F8) can be very useful in debugging a sheet of logical errors as you can follow exactly how FREE & EASY does the calculations and you see what is in the cell contents of the cell to be evaluated (in the second bar from the top of the screen). The F9 key is used to calculate all the cells in all the sheets. As FREE & EASY is calculating the cells, it displays which cells in which sheet are being calculated. The shift-F9 key combination calculates all the cells in the active sheet (file) i.e. the sheet in which the cursor in found. These keys are useful if you make a change in a cell or variable and want to see how many cells are affected. The F10 key calculates from the first cell to be evaluated (in the first sheet) up to the present cursor location. The shift-Fl0 key calculates from the present cursor location to the last cell to be evaluated in the last sheet. The shift-F10 key is useful because it allows you to go through all the cells in the sheet. If a cell has an evaluation error, the evaluation stops at that cell. You can correct that cell then continue evaluating cells with shift-Fl0 without having to start the evaluation all over again. Again these commands show which cell is being calculated during the evaluation. Evaluation Errors Even though FREE & EASY checks for correct syntax when adding a new cell or modifying a cell, it is still possible to get an evaluation error. Evaluation errors can be division by 0, variables that cannot be found, argument of a function that is not the right value, etc. A full list of evaluation errors is in appendix B. If a cell has an evaluation error, its value is set to zero (0.0). When adding a cell or modifying a cell and a syntax error is present, FREE & EASY does not allow the cell to be added or modified. However, when adding a cell or modifying a cell and an evaluation error in encountered, FREE & EASY allows the cell to be added or modified but gives the cell the value zero. When evaluating cells and an evaluation error happens in a cell, the evaluation of cells stops and an error message is displayed. The message tells in which cell the error is found and what the error is. At this point, you can press F1 to get a help screen with further explanation of that error. Remember helps are context sensitive and every evaluation error has its own help screen which can be accessed with F1 (or from the help index if desired). When you leave the error page 6-9 message, the cursor will go to the cell which has the error. You can correct the error at this point and continue the cell evaluation with shift-F10. Remember also that you can always bring up the last error with shift F1 or with the "last Error" menu item in the "Help" pull- down menu. Calculation Order of Sheets We have already seen that the cells in a sheet are calculated as you would read a page, from left to right and top to bottom. Each sheet that is loaded is calculated sequentially. At the top of the window is the name of the sheet (file) contained in that window and its order in the calculation of sheets. If at the top of the window you see "3:FILE.FRE" this means that the sheet named "FILE.FRE" is evaluated third. Whenever you start a new sheet or load a sheet from the disk, that file is added to the end of the evaluation order. You can change this order at any time by using the "calc Order ..." menu item in the "Eval" pull- down menu. Use the cursor control keys to highlight the sheet whose order you wish to change and press Enter. Next highlight the new position in the evaluation order you wish for this sheet and press Enter. Press Escape to leave this feature. The prompt bar at the bottom of the screen will help you with this procedure. Evaluation Defaults You have the option of changing many of the ways in which FREE & EASY evaluates the cells. These options are changed with the "evaluation Defaults ..." menu item in the "Eval" pull-down menu. Each of the evaluation features is discussed. The "Trig functions" menu item allows you to calculate trig functions (e.g. SIN) and inverse trig functions (e.g. ASN) in "radians", "degrees" or "gradients". PI (3.14159) radians equals 180 degrees equals 200 gredients. When FREE & EASY is evaluating a cell and it encounters a name, this can be a cell name or a variable name. The "Search priority for names" menu item tells FREE & EASY to start searching for a "cell" or a "variable" by that name. As soon as one is found by that name, its value is used in the calculations. If no variable is found by that name, FREE & EASY starts looking at the cell names and vice versa. If the menu item "checK cell evaluation order" is set to "yes" then when another cell is referred to in the cell contents (by name or location in the sheet), then that cell must have been evaluated previously, i.e. in a sheet before in the evaluation order or above or to the left of that cell. Otherwise an evaluation error message is given. If set to "no" this check is not done. It is recommended to keep this menu item set to "yes" (unless you have a specific reason not to); otherwise a confusing situation could result. If the "search all files for Cell names" is set to "yes", then all page 6-10 sheets loaded will be searched for a cell name. If set to "no", then only the sheet containing the cell being evaluated will be searched for the cell name. The same principle applies to the "search all files for Variable names" and "search all files for Range names" menu items. For these menu items, if only one sheet is loaded, their setting has no effect. The "assIgn variable to cell value" menu item if set to "yes" allows you to assign a variable the value of a cell with the equal sign (exactly the same way you would assign a number to the variable). If this is set to "no" then the display value of the cell would equal the value of the variable. It is only rarely that you would set this item to "yes". In most cases, it makes more sense to use the cell name to refer to the value of that cell. Once you have adjusted all menu items in the "Evaluation Defaults" pop- up menu, select the "Accept these defaults" menu item. To change the evaluation defaults you can cancel by pressing Escape. Ranges We have already talked briefly about what a range is and what it is used for in the section "Range Functions" in this chapter. In summary, a range is a rectangular part of the sheet which you define and give a name. Ranges will now be looked at in more detail to see how they are added, deleted, modified, shown and renamed. All features used in working with ranges are found in the "Range" pull- down menu. Ranges take up very little memory. A range almost always takes less computer memory than a cell. Memory for ranges is allocated dynamically i.e. allocated when you add the range. There is no limit to the number of ranges which can be added. Range Address in the Cell Contents A range in the cell contents can be defined by a range address. A range address contains the sheet location of two opposite corners of a rectangular area of the sheet. e.g. !0005!0010-!0015!0020 The first set of two numbers are the row and column of one corner of the range and the second set of two numbers are the row and column of the opposite corner. These locations are separated by a '-' character. These locations can be relative, in which case the '!' character is used, or absolute in which case the '$' character is used. If desired, one corner can be made relative and the opposite corner absolute. One of the sample sheets in the FREE & EASY package gives an example where this is useful. When referring to a range by an address, it must be in the same sheet as the cell is in. (If you wish to refer to a range on another sheet you must use a range name) When entering a range address this format must be followed exactly; otherwise a syntax error will be given. page 6-11 When entering the cell contents and you press the '\' key and the cursor is next to a range function, e.g. SUM(, the menu will disappear and you will be in the highlight range mode. In this case move the cursor to the desired location, press F7 and then define the range you wish. Press Enter to accept the range address as relative, control- Enter to accept the range address as absolute or Escape to cancel. (The prompt bar gives you the instructions for this) The range will be entered in the cell contents in the correct format. When you press the '\' key while entering the cell contents and the cursor is on a valid range address, the range will initially be shown. From here you can adjust the range as desired. Adding Ranges To add a new range to the sheet, use the "Add" menu item on the "Range" pull-down menu. First you are asked to give the range a name. Every range must have a name. If you try to proceed without giving a range name, you will get the "there must be a range name entered" error message. Each range in a sheet must also have a unique name. If you give a range name that has already been used, you will get the "the range name entered is already used" error message. There is no restriction on what the range name can be (unlike a cell name or a variable name) other than a maximum of 20 characters for the name. It is recommended, however, that you use a meaningful name. Once the range name has been entered, use the cursor to define the range. A highlighted rectangular box is seen on the screen. The cursor is in one corner of the box. Move the cursor to adjust the size of the box. The corner opposite the corner with the cursor is fixed. Press F5 or F6 to change the corner of the box in which the cursor is found. You can also press F7 to change mode where the cursor control keys move the whole highlighted box rather than just the box size. Ranges can overlap each other if desired. Press Enter to accept the range or Escape to cancel. After the range has been added to the sheet, the range will be highlighted on the sheet. You are encouraged to use text on the sheet to help show where the range is and what its name is. Contents of a Range Remember that a range function does some sort of manipulation or calculation on all values found in the range. (See section on "Range Functions" in this chapter for details). Therefore it is important to know what sort of values are allowed in the range. In general, four types of values are allowed in the range. These are numbers, variable names, cell names and cells. A variable cannot be defined within a range. When evaluating a range function and FREE & EASY encounters a number, the program checks to ensure that the number is valid. If not, the evaluation error message "invalid number" or "number too large or too small" is given. If a name is found, FREE & EASY searches for a cell or variable of that name page 6-12 and uses its value in the calculations. (See section "Evaluation Defaults" for details about how FREE & EASY searches for these). If none is found, the evaluation error "cannot find variable or cell name" is given. Because of the restrictions as to what can be within a range, descriptive text cannot be within the range boundaries. However, it can be just outside the range boundaries. Remember to ensure that all data you want in the range is completely within the range i.e. Do not straddle the range borders with names, numbers or cells. Range Menu The menu items "Delete ...", "Resize/move ...", "reName ..." and "Show ..." in the "Range" pull-down menu all lead to a pop-up menu where all the ranges are shown for the active sheet. This menu gives the name of the file and its evaluation order number, the name of each range along with the location of the range. This is given as ur (upper row), uc (upper column), lr (lower row) and lc (lower column). Use the cursor control keys to scroll through the ranges and highlight the desired range. Press Enter to do the action or Escape to cancel. You can also press F5 which will show the ranges in the next sheet. Delete Ranges The "Delete ..." menu item leads to the pop-up menu for deleting ranges. Highlight the range that you wish to delete and press Enter. You can delete as many ranges as you wish. Press Escape to leave the menu. You can delete all the ranges in the active sheet with the "deLete all ranges" menu item. Before all ranges are deleted, you are asked to confirm that this is what you really want to do. Resize/Move Ranges. The "Resize/move ..." menu item leads to the pop-up menu for modifying existing ranges. Highlight the range you want to modify and press Enter. (You can press Escape to cancel also). The range you choose will be highlighted on the screen. Adjust the size and/or position the same way as you do for adding ranges. Press Enter when the range is the way you want it. You can also press Escape to cancel the changing of the range. Renaming Ranges The "reName ..." menu item leads to the pop-up menu for modifying existing ranges. Highlight the range you wish to rename and press Enter. You can now type in a new name. Rename as many ranges as you wish. Press Escape to leave the menu. page 6-13 Showing Ranges Only one range can be shown at a time. If you add a range or resize or move a range when you finish the operation, the range you were working on remains highlighted once the operation is finished. The "Show ..." menu item is used to highlight a range. Highlight the range you wish to show and press Enter (you can press Escape to cancel this operation). The cursor will go to the upper left corner of the range you choose, even if the active sheet has to be changed. The range will remain highlighted until you choose another range to be shown, add a range, resize and move a range or choose the "Highlight" command in order to perform an editing function. You can also choose the "show range Off" menu item to stop showing a range.